package cn.schoolwow.quickdao.module.database.ddl.query.flow.property.getEntityPropertyList;

import cn.schoolwow.quickdao.annotation.IdStrategy;
import cn.schoolwow.quickdao.domain.entity.Entity;
import cn.schoolwow.quickdao.domain.entity.Property;
import cn.schoolwow.quickdao.module.database.parent.domain.ResultSetConsumer;
import cn.schoolwow.quickdao.module.database.parent.flow.executor.ExecuteQueryConnectionFlow;
import cn.schoolwow.quickflow.domain.FlowContext;
import cn.schoolwow.quickflow.flow.BusinessFlow;

import java.sql.ResultSet;
import java.util.List;

public class GetEntityPropertyListBySQLServerFlow implements BusinessFlow {
    @Override
    public void executeBusinessFlow(FlowContext flowContext) throws Exception {
        List<Entity> entityList = (List<Entity>) flowContext.checkData("entityList");

        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表字段类型信息")
                .putTemporaryData("sql", "select table_name,ordinal_position,column_name,column_type,is_nullable from information_schema.columns;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("table_name");
                            for(Entity entity:entityList){
                                if(!entity.tableName.equalsIgnoreCase(tableName)){
                                    continue;
                                }
                                Property property = new Property();
                                property.column = resultSet.getString("column_name");
                                property.columnType = resultSet.getString("column_type");
                                property.notNull = "NO".equals(resultSet.getString("is_nullable"));
                                entity.properties.add(property);
                            }
                        }
                    }
                })
                .execute();

        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表字段注释")
                .putTemporaryData("sql", "select b.name table_name, c.name column_name, convert(varchar(255),a.value) value from sys.extended_properties a, sysobjects b, sys.columns c where a.major_id = b.id and c.object_id = b.id and c.column_id = a.minor_id;")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("table_name");
                            for(Entity entity:entityList){
                                if(!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }
                                String columnName =  resultSet.getString("column_name");
                                for (Property property : entity.properties) {
                                    if (!property.column.equalsIgnoreCase(columnName)) {
                                        continue;
                                    }
                                    property.comment = resultSet.getString("value");
                                }
                            }
                        }
                    }
                })
                .execute();

        flowContext.startFlow(new ExecuteQueryConnectionFlow())
                .putTemporaryData("name", "获取表主键")
                .putTemporaryData("sql", "select table_name, column_name from information_schema.key_column_usage")
                .putReturnData("resultSetConsumer",new ResultSetConsumer() {
                    @Override
                    public void consumeResultSet(ResultSet resultSet) throws Exception {
                        while(resultSet.next()){
                            String tableName = resultSet.getString("table_name");
                            for(Entity entity:entityList){
                                if(!entity.tableName.equalsIgnoreCase(tableName)) {
                                    continue;
                                }
                                String columnName =  resultSet.getString("column_name");
                                for (Property property : entity.properties) {
                                    if (!property.column.equalsIgnoreCase(columnName)) {
                                        continue;
                                    }
                                    property.id = true;
                                    property.strategy = IdStrategy.AutoIncrement;
                                }
                            }
                        }
                    }
                })
                .execute();
    }

    @Override
    public String name() {
        return "获取数据库表字段列表-SQLServer数据库";
    }
}
